﻿CREATE PROCEDURE [dbo].[SP_GetMessageClip]
	-- Add the parameters for the stored procedure here
	@userid int,
	@type int,
	@start int,
	@limit int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	if(@type=0)
		begin
			SELECT C.*
			FROM(
				SELECT B.*
				from(
					SELECT ROW_NUMBER() over(order by A.Type asc, A.LastestUpdatedTime Desc) as RowIndex,
						A.*,
						A1.AvatarUrl as LastestSenderAvatarUrl,
						A1.NickName as LastestSenderNickName
					FROM MessageClip as A
						LEFT JOIN Account as A1 on A1.Id = A.LastestSenderId
					WHERE A.ReceiverId = @userid and A.isDeleted = 0
				) AS B 
				WHERE B.RowIndex > @start and B.RowIndex < @start+@limit
			) AS C;
			update MessageClip set NewMessageCount = 0 WHERE ReceiverId = @userid  and isDeleted = 0;
		end
	else
		begin
			SELECT C.*
			FROM(
				SELECT B.*
				from(
					SELECT ROW_NUMBER() over(order by A.LastestUpdatedTime Desc) as RowIndex,
						A.*,
						A1.AvatarUrl as LastestSenderAvatarUrl,
						A1.NickName as LastestSenderNickName
					FROM MessageClip as A
						LEFT JOIN Account as A1 on A1.Id = A.LastestSenderId
					WHERE A.ReceiverId = @userid and A.Type=@type and A.isDeleted = 0
				) AS B 
				WHERE B.RowIndex > @start and B.RowIndex < @start+@limit
			) AS C;
			update MessageClip set NewMessageCount = 0 WHERE ReceiverId = @userid and [Type]=@type and isDeleted = 0;
		end
END